package com.infore.dataReceiving.export.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONObject;
import com.infore.dataReceiving.bo.ExportExcelBO;
import com.infore.dataReceiving.util.ArrayUtils;

public class ExportUtil {
	
	@SuppressWarnings("unused")
	private final static Logger logger = LoggerFactory.getLogger(ExportUtil.class);
	private final static String[] parsePatterns = {"yyyy-MM-dd",
            "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy/MM/dd",
            "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyyMMdd"};
	
	@SuppressWarnings("deprecation")
    public static HSSFWorkbook getExcel(String title, ExportExcelBO exportExcelBo, List<Map<String, Object>> dataset, String pattern,
            String systemName) {
        // 声明一个工作薄
        pattern = StringUtils.defaultString(pattern, "yyyy-MM-dd");
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 生成表头样式
        CellStyle headStyle = headStyle(workbook);
        // 生成并设置另一个样式
        CellStyle contentStyle = contentStyle(workbook);
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        int rowCount = 0;
        if (StringUtils.isNotBlank(systemName)) {
            createCell(rowCount, systemName, sheet, workbook, exportExcelBo.getMaxCol(), true);
            rowCount++;
        }
        // 产生表格标题行
        HSSFRow row = sheet.createRow(rowCount);
        // 获得头内容
        getHeader(exportExcelBo, sheet, headStyle, row);
        // 表头内容信息获取完之后 将行数设置为表头的最大行数,因为有标题,行数需要加1
        rowCount = exportExcelBo.getMaxRow() + 1;
        // 获取映射对应的字段名
        List<String> fieldPropertyList = exportExcelBo.getFieldPropertyList();
        // 遍历集合数据，产生数据行
        for (int index = 0; index < dataset.size(); index++) {
            row = sheet.createRow(rowCount++);
            row.setHeight((short) 500);
            Map<String, Object> map = dataset.get(index);
            // 频繁反射影响性能，使用map字段匹配
            for (int i = 0; i <= fieldPropertyList.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(contentStyle);
                // 判断值的类型后进行强制类型转换
                Object value = null;
                String textValue = null;
                if (i == 0) {
                	value = (index + 1) + "";
                } else {
                	String fieldName = fieldPropertyList.get(i - 1);
                	value = map.get(fieldName.replaceAll("\"", ""));
                }
                if (value instanceof Boolean) {
                    boolean bValue = (Boolean) value;
                    textValue = "是";
                    if (!bValue) {
                        textValue = "不是";
                    }
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else if (value instanceof byte[]) {
                    // 有图片时，设置行高为60px;
                    row.setHeightInPoints(60);
                    // 设置图片所在列宽度为80px,注意这里单位的一个换算
                    sheet.setColumnWidth(i, (short) (35 * 80));
                    // sheet.autoSizeColumn(i);
                    byte[] bsValue = (byte[]) value;
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
                    anchor.setAnchorType(2);
                    patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                } else if (value != null) {
                    // 其它数据类型都当作字符串简单处理
                    textValue = value.toString();
                }
                // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                if (textValue != null) {
                    Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                    Matcher matcher = p.matcher(textValue);
                    if (matcher.matches() || (value instanceof Double)) {
                        // 是数字当作double处理
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                }
            }
        }
        return workbook;
    }
	
	/**
     * 以流的形式导出excel，数据（包含合并单元格信息）从前台以表单请求传输.
     * <p>
     * 使用该方法请前台请求格式请按照excelDataFormat.js发送
     * </p>
     * 
     * @author zhoumx
     * @param exportdDto 导出参数实体
     * @param response 请求响应
     * @return
     * @throws UnsupportedEncodingException 编码异常
     * @throws InterruptedException 中断异常
     */
    public static String exportIO(String title, ExportExcelBO exportExcelBo, List<Map<String, Object>> dataset, String pattern,
            String systemName, HttpServletResponse response) throws UnsupportedEncodingException, InterruptedException {

        byte[] content = new byte[0];
        try {
            content = exportStatisticsData(title, exportExcelBo, dataset, pattern, systemName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 响应头
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out;
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        // 写入
        try {
            out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (bis != null) {
                    bis.close();
                }
                if (bos != null) {
                    bos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    
    /**
     * 单个表格信息导出.
     * 
     * @author zhoumx
     * @param exportdDto 表格导出实体
     * @return
     * @throws IOException IO读写异常
     */
    public static byte[] exportStatisticsData(String title, ExportExcelBO exportExcelBo, List<Map<String, Object>> dataset, String pattern,
            String systemName) throws IOException {
        HSSFWorkbook wb = getExcel(title, exportExcelBo, dataset, pattern, systemName);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        byte[] bytes = null;
        try {
            wb.write(os);
            bytes = os.toByteArray();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return bytes;
    }
	
	/**
     * 获取头内容(手动配置表头合并的信息)
     * 
     * @param headers
     * @param sheet
     * @param style
     * @param row
     */
    private static void getHeader(ExportExcelBO exportExcelBo, HSSFSheet sheet, CellStyle style, HSSFRow row) {
//    	List<String> aList = Arrays.asList("序号", "日期", "零点校正液核查", "", "", "", "量程校正液核查", "", "", "");
//		List<String> bList = Arrays.asList("", "", "测定结果", "", "绝对误差", "绝对误差范围", "测定结果", "", "相对误差", "相对误差范围");
//		List<String> cList = Arrays.asList("", "", "系统", "零点校正液浓度", "", "", "系统", "量程校正液浓度", "", "");
//		
//		List<String> aaList = Arrays.asList("1, 3, 0, 0", "1, 3, 1, 1", "1, 1, 2, 5", "1, 1, 6, 9");
//		List<String> bbList = Arrays.asList("2, 2, 2, 3", "2, 3, 4, 4", "2, 3, 5, 5", "2, 2, 6, 7", "2, 3, 8, 8", "2, 3, 9, 9");
    	List<List<String>> headList = exportExcelBo.getHeadList(); // 表头的文本值集合
    	List<List<String>> mergeList = exportExcelBo.getMergeList(); // 表头的合并信息集合
    	int mergeNum = mergeList.size();
    	List<String> mergeSingeList = null;
    	for (int i = 0; i < headList.size(); i++) {
			row = sheet.createRow(i + 1);
			row.setHeight((short) 700);
			for (int j = 0; j < headList.get(i).size(); j++) {
				Cell tempCell = row.createCell(j);
			    tempCell.setCellValue(headList.get(i).get(j));
			    tempCell.setCellStyle(style);
			}
			if (mergeList != null && mergeList.size() > 0) { // 不为空代表表头需要合并
				if (i < mergeNum) {
					mergeSingeList = mergeList.get(i);
				}
				if (mergeSingeList != null && mergeSingeList.size() > 0) {
					for (String string : mergeSingeList) {
						String[] temp = string.split(",");
						Integer startrow = Integer.parseInt(temp[0].trim());
				        Integer overrow = Integer.parseInt(temp[1].trim());
				        Integer startcol = Integer.parseInt(temp[2].trim());
				        Integer overcol = Integer.parseInt(temp[3].trim());
				        sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
					}
				}
			}
		}
    }
    
    /**
     * 获取头内容
     * 
     * @param headers
     * @param sheet
     * @param style
     * @param row
     */
    private static void getTrendsHeader(List<List<JSONObject>> table, HSSFSheet sheet, CellStyle style, HSSFRow row, int maxCol) {
    	for (int i = 0; i < table.size(); i++) {
			row = sheet.createRow(i + 1);
			row.setHeight((short) 700);
			//表格每行的按照最大列数进行创建
			for (int k = 0; k < maxCol; k++) {
				row.createCell(k).setCellStyle(style);
			}
			for (int j = 0; j < table.get(i).size() - 1; j++) {
			    int col1 = Integer.parseInt(table.get(i).get(j).get("col").toString());          //单元格列绝对位置
				int row2 = Integer.parseInt(table.get(i).get(j).get("row").toString());      //单元格行绝对位置
//				int width = Integer.parseInt(table.get(i).get(j).get("width").toString());   //单元格宽度
//				int height = Integer.parseInt(table.get(i).get(j).get("height").toString());   //单元格高度
				int colspan = Integer.parseInt(table.get(i).get(j).get("colspan").toString());   //合并列
				int rowspan = Integer.parseInt(table.get(i).get(j).get("rowspan").toString()); //合并行
				String val = table.get(i).get(j).get("val").toString(); // 文本值
				//根据单元格的绝对位置坐标进行合并
				if(rowspan > 0 || colspan > 0){
					sheet.addMergedRegion(new CellRangeAddress(row2 + 1, row2 + rowspan + 1, col1, col1 + colspan)); //四个坐标按顺序分别表示：起始行、结束行、起始列、结束列
				}
				HSSFCell cell = null;
				cell = row.getCell(col1);  //获取单元格
				cell.setCellStyle(style); //单元格样式设置
				cell.setCellValue(val); //单元格值设置
			}
		}
    }
	
	/**
     * 生成头样式
     * 
     * @param workbook
     * @return
     */
    private static HSSFCellStyle headStyle(HSSFWorkbook workbook) {
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 设置这些样式
        Font headerFont = workbook.createFont();
		headerFont.setFontName("微软雅黑");
		headerFont.setFontHeightInPoints((short) 14);
		headerFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		headerFont.setColor(HSSFColor.BLACK.index);
		
		headerStyle.setFont(headerFont);
		headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
		headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
		headerStyle.setLocked(true);
		headerStyle.setWrapText(false);// 自动换行
		
		headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
		headerStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); 
        return headerStyle;
    }
    
    /**
     * 生成内容样式
     * 
     * @param workbook
     * @return
     */
    private static HSSFCellStyle contentStyle(HSSFWorkbook workbook) {
        HSSFCellStyle commonStyle = workbook.createCellStyle();
        
        Font contextFont = workbook.createFont();
		contextFont.setFontName("微软雅黑");
		contextFont.setFontHeightInPoints((short) 10);
		contextFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		contextFont.setColor(HSSFColor.BLACK.index);
		
		commonStyle.setFont(contextFont);
		commonStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		commonStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		commonStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		commonStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		commonStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		commonStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		commonStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		commonStyle.setLocked(true);
		commonStyle.setWrapText(true);// 先设置为自动换行
		commonStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
		commonStyle.setFillForegroundColor(HSSFColor.WHITE.index); 
        
        return commonStyle;
    }
	
	/**
     * 创建一行且单元格以最大列数合并，内容居中，字体设置开关.
     * 
     * @param row 第n行 设置(n-1)
     * @param content 单元格内容
     * @param sheet 工作页
     * @param workbook 工作簿
     * @param maxcol 最大列数
     * @param flag 是否设置字体样式 true：设置字体20px，加粗
     */
    public static void createCell(int row, String content, HSSFSheet sheet, HSSFWorkbook workbook, int maxcol, boolean flag) {
        HSSFRow titleRow = sheet.createRow(row);
        titleRow.setHeight((short) ((35 * 15.625)));
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        // 设置内容垂直居中、水平居中
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
        titleStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); 
        for (int k = 0; k < maxcol; k++) {
        	titleRow.createCell(k).setCellStyle(titleStyle);
        }
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(row, row, 0, maxcol - 1));
        HSSFCell cell = titleRow.getCell(0);
        cell.setCellValue(content);
        if (flag) {
            // 设置字体大小、加粗
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setBoldweight((short) 10);
            titleStyle.setFont(font);
        }
    }
    
	/**
	* @Title: exportExcel
	* @Description: 导出单个表格数据
	* @param request
	* @param list 表内容数据
	* @param headerProp 表的映射字段
	* @return    参数
	* @return byte[]    返回类型
	* @throws
	 */
	@SuppressWarnings({ "deprecation", "unchecked" })
	public static byte[] exportExcel(HttpServletRequest request, List<Map<String, Object>> list, String headerProp, String pattern){
		String tableData = request.getParameter("tableData");
		JSONObject json = JSONObject.parseObject(tableData);
		List<List<JSONObject>> headTableData = (List<List<JSONObject>>) json.get("data"); //获取表头数据
		
		int maxcol = Integer.parseInt(json.get("maxcol").toString());  //获取最大列数
		String fileName = json.get("name").toString();  //表名
		
		HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
		
        // 生成表头样式
        CellStyle headStyle = headStyle(workbook);
        // 生成并设置另一个样式
        CellStyle contentStyle = contentStyle(workbook);
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        int rowCount = 0;
        if (StringUtils.isNotBlank(fileName)) {
            createCell(rowCount, fileName, sheet, workbook, maxcol, true);
            rowCount++;
        }
        // 产生表格标题行
        HSSFRow row = sheet.createRow(rowCount);
        // 获得头内容
        getTrendsHeader(headTableData, sheet, headStyle, row, maxcol);
        // 表头内容信息获取完之后 将行数设置为表头的最大行数,因为有标题,行数需要加1
        rowCount = headTableData.size() + 1;
        
        // 获取映射对应的字段名
        List<String> fieldPropertyList = ArrayUtils.stringToList(headerProp);
        // 遍历集合数据，产生数据行
        for (int index = 0; index < list.size(); index++) {
            row = sheet.createRow(rowCount++);
            row.setHeight((short) 500);
            Map<String, Object> map = list.get(index);
            // 频繁反射影响性能，使用map字段匹配
            for (int i = 0; i <= fieldPropertyList.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(contentStyle);
                // 判断值的类型后进行强制类型转换
                Object value = null;
                String textValue = null;
                if (i == 0) {
                	value = (index + 1) + "";
                } else {
                	String fieldName = fieldPropertyList.get(i - 1);
                	value = map.get(fieldName.replaceAll("\"", ""));
                }
                if (value instanceof Boolean) {
                    boolean bValue = (Boolean) value;
                    textValue = "是";
                    if (!bValue) {
                        textValue = "不是";
                    }
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else if (value instanceof byte[]) {
                    // 有图片时，设置行高为60px;
                    row.setHeightInPoints(60);
                    // 设置图片所在列宽度为80px,注意这里单位的一个换算
                    sheet.setColumnWidth(i, (short) (35 * 80));
                    // sheet.autoSizeColumn(i);
                    byte[] bsValue = (byte[]) value;
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
                    anchor.setAnchorType(2);
                    patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                } else if (value != null) {
                	// 其它数据类型都当作字符串简单处理
                	if (parseDate(value.toString())) { // 判断字符串是否为时间格式
                    	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    	Date date = null;
    					try {
    						date = sdf.parse(value.toString());
    					} catch (ParseException e) {
    						e.printStackTrace();
    					}
                    	textValue = sdf.format(date);
                    } else {
                    	textValue = value.toString();
                    }
                }
                // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                if (textValue != null) {
                    Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                    Matcher matcher = p.matcher(textValue);
                    if (matcher.matches() || (value instanceof Double)) {
                        // 是数字当作double处理
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                }
            }
        }
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		byte[] bytes = null;
        try {
        	workbook.write(os);
        	bytes = os.toByteArray();
            os.close(); 
        } catch (IOException e) {
            e.printStackTrace();
        }
		return bytes;
	}
	
	public static boolean parseDate(String value) {
		// 设置日期转化成功标识
		boolean dateflag = true;
		// 这里要捕获一下异常信息
		try {
			DateUtils.parseDate(value.toString(), parsePatterns);
		} catch (Exception e) {
			dateflag = false;
		}
		// 成功：true ;失败:false
		return dateflag;
	}
}
